"""
Module with utility methods and classes that generate or execute SQL
statements.

@author: Wei Chih Lu
"""

import logging
from springpython.database.core import DatabaseTemplate
from springpython.database import DataAccessException
from springpython.database.transaction import ConnectionFactoryTransactionManager,\
    TransactionTemplate, TransactionCallbackWithoutResult, TransactionCallback
from springpython.database.factory import SQLServerConnectionFactory,\
    MySQLConnectionFactory, Sqlite3ConnectionFactory

_LOGGER = logging.getLogger("mx.org.came.sql_utils")

class SQLScriptRunner():
    """Class that uses an instance of SpringPython's ``ConnectionFactory``
    to execute SQL statements.
    """
    
    def __init__(self, db_connection_factory):
        self._db_connection_factory = db_connection_factory
    
    def run_script(self, sql, is_fetch):
        """Executes the sql statement passed. Should an error occur,
        DataAccessException is raised.
        """
        # Only delimiters for MS sql Server, MySQL and SQLite are supported.
        # in case MySQL is being used, replace the brackets used by MS sql
        # by MySQL's apostrophes. Do something similar for SQLite.
        if isinstance(self._db_connection_factory
                      , MySQLConnectionFactory):
            sql=sql.replace('[','`').replace(']','`')
        elif isinstance(self._db_connection_factory
                      , Sqlite3ConnectionFactory):
            sql=sql.replace('[','\'').replace(']','\'')
        _LOGGER.debug("SQL statement to execute: %s", sql)
        database_template = DatabaseTemplate(self._db_connection_factory)
        txManager = ConnectionFactoryTransactionManager(self._db_connection_factory)
        txTemplate = TransactionTemplate(txManager)
        if is_fetch:
            class txDefinition(TransactionCallback):
                def do_in_transaction(self, status):
                    results = database_template.query_for_list(sql)
                    _LOGGER.debug("Number of entries retrieved by the query: %s"
                                  , len(results))
                    return results
            return txTemplate.execute(txDefinition())
        else:
            class txDefinition(TransactionCallbackWithoutResult):
                def do_in_tx_without_result(self, status):
                    rows_affected = database_template.execute(sql)
                    _LOGGER.debug("Number of rows affected %s", rows_affected)
            return txTemplate.execute(txDefinition())
    
    def get_application_descriptor_path(self,id):
        SQL="""
            SELECT `path`
            FROM `Info`
            WHERE `id` ={0}
            """.format(id)
        return self.run_script(SQL, True)
    
    def get_form_descriptor_path(self,aid,fid):
        SQL="""
            SELECT `path`
            FROM `Info`
            WHERE `aid` ={0} and `fid`={1}
            """.format(aid,fid)
        return self.run_script(SQL, True)
    
def get_insert_sql(decoded_query):
    """Receives a map with the data from a decoded insert query and
    returns the SQL statement for it. The dictionary should not include the
    ``dbName`` key. See the ``codec`` module for information on the dictionary's
    contents.
    """
    schema=decoded_query.pop('schemaName')
    try:
        decoded_query.pop('dbName')
    except KeyError:
        _LOGGER.debug("The key 'dbName' has already been removed from "
                      "decoded_query")
    sqlCol,sqlVal,update="","",""
    for col in decoded_query.iterkeys():
        sqlCol +="[{0}],".format(col)
        val=str(decoded_query.get(col))
        if val.isdigit():
            sqlVal +="{0},".format(val)
        else:
            sqlVal +="'{0}',".format(val) # FIXME - escape characters verify ' can delimite strings in SQL Server
        val="[{0}]".format(val)
        # FIXME - This is bound to CAME's tables and should be general!
        if col!="Client ID" or col!="Group ID" or col!="Week's Number": # It is the Primary Key
            update += "{0}={1},".format("[{0}]".format(col),val)
    sqlCol=sqlCol.rstrip(',')
    sqlVal=sqlVal.rstrip(',')
    update=update.rstrip(',')
    sql="INSERT INTO [{0}] ({1}) VALUES ({2})".format(schema,sqlCol,sqlVal,update)
    return sql
    
def get_select_sql(decoded_query, fetch_list, logical):
    """Receives a map with the data from a decoded selection query, a list
    with the fields to fetch and the logical constrain to be used with
    the values provided (equality, etc.) and returns an SQL statement.
    See the ``codec`` module for information on the dictionary's
    contents.
    """
    schema = decoded_query.pop('schemaName')
    db = decoded_query.pop('dbName')
    fetch = ""
    constrain = ""
    for field in fetch_list:
        fetch += "[{0}], ".format(field)
    fetch = fetch.rstrip(", ")
    for (k, v) in decoded_query.iteritems():
        _LOGGER.debug((k, v))
        constrain += " [{0}]{1} ".format(k,v)
        constrain += logical
        #print constrain
    constrain = constrain.rstrip(logical).strip()
    sql = "SELECT {0} FROM [{1}] WHERE {2}".format(fetch, schema, constrain)
    return sql

def get_delete_sql(decoded_query):
    """Receives a map with the data from a decoded deletion query and returns
    an SQL statement for it.
    See the ``codec`` module for information on the dictionary's
    contents.
    """
    schema=decoded_query.pop('schemaName')
    db=decoded_query.pop('dbName')
    for (k,v) in decoded_query.iteritems():
        constrain="[{0}]={1}".format(k,v)
    sql="DELETE FROM [{2}].[{0}] WHERE {1}".format(schema,constrain,db)
    return sql
